-- Create crawler_configs table for managing crawler sources
CREATE TABLE IF NOT EXISTS crawler_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_name VARCHAR(100) UNIQUE NOT NULL,
    url_template TEXT NOT NULL,
    frequency INTEGER DEFAULT 3600, -- in seconds
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'paused', 'error')),
    retry_times INTEGER DEFAULT 3,
    timeout INTEGER DEFAULT 30,
    headers JSONB DEFAULT '{}',
    rate_limit INTEGER DEFAULT 10, -- requests per minute
    proxy_enabled BOOLEAN DEFAULT FALSE,
    user_agent_type VARCHAR(20) DEFAULT 'mixed' CHECK (user_agent_type IN ('desktop', 'mobile', 'mixed')),
    auth_config JSONB DEFAULT NULL, -- for sites requiring authentication
    custom_settings JSONB DEFAULT '{}', -- additional Scrapy settings
    last_run_at TIMESTAMP,
    last_success_at TIMESTAMP,
    last_error TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better query performance
CREATE INDEX idx_crawler_source ON crawler_configs(source_name);
CREATE INDEX idx_crawler_status ON crawler_configs(status);
CREATE INDEX idx_crawler_last_run ON crawler_configs(last_run_at);

-- Create trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_crawler_configs_updated_at 
    BEFORE UPDATE ON crawler_configs 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Insert some initial crawler configurations
INSERT INTO crawler_configs (source_name, url_template, frequency, status, headers, rate_limit) VALUES
('weibo', 'https://weibo.com/ajax/statuses/topic_band?q={keyword}&page={page}', 1800, 'active', '{"Accept": "application/json"}', 5),
('zhihu', 'https://www.zhihu.com/api/v4/search_v3?q={keyword}&type=content&offset={offset}', 3600, 'active', '{"Accept": "application/json"}', 10),
('toutiao', 'https://www.toutiao.com/api/search/content/?keyword={keyword}&offset={offset}', 2400, 'active', '{"Accept": "application/json"}', 8)
ON CONFLICT (source_name) DO NOTHING;